﻿

namespace System.Data
{
    /// <summary>
    /// DbHelper
    /// </summary>
    public class DbHelper
    {
        #region Properties

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string ConnectionString { get; protected set; }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        public System.Data.IDbConnection Connection { get; protected set; }

        /// <summary>
        /// 获取数据库事务
        /// </summary>
        public System.Data.IDbTransaction Transaction { get; protected set; }

        /// <summary>
        /// DbProviderFactory
        /// </summary>
        public System.Data.DbProviderFactory Provider { get; private set; }

        #endregion

        #region DbHelper

        /// <summary>
        /// 初始化
        /// </summary>
        private DbHelper() { }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="connectionString"></param>
        public DbHelper(DatabaseType dbType, string connectionString = null)
        {
            //  实例化 DbProviderFactory
            this.Provider = new DbProviderFactory(dbType);
            this.ConnectionString = connectionString ?? string.Empty;
            //  实例化 IDbConnection
            this.Connection = this.Provider.CreateConnection();
            this.Connection.ConnectionString = this.ConnectionString;
        }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="connection"></param>
        public DbHelper(DatabaseType dbType, IDbConnection connection)
        {
            this.Provider = new DbProviderFactory(dbType);
            this.Connection = connection != null ? connection : this.Provider.CreateConnection();
            this.ConnectionString = this.Connection.ConnectionString;
        }

        #endregion

        #region CreareCommandInfo

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public CommandInfo CreareCommandInfo(string cmdText)
        {
            return this.CreareCommandInfo(cmdText, new System.Collections.Generic.List<IDataParameter>());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="format"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public CommandInfo CreareCommandInfoFormat(string format, params  object[] args)
        {
            System.Collections.Generic.List<IDataParameter> parameters = new Collections.Generic.List<IDataParameter>();
            System.Collections.Generic.List<string> formatArgs = new Collections.Generic.List<string>();

            if (args != null && args.Length > 0)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    var param1 = this.Provider.CreateParameter();
                    param1.ParameterName = string.Format("param{0}", i);
                    param1.Value = args[i];

                    this.Provider.ValidParameter(param1);
                    parameters.Add(param1);
                    formatArgs.Add(param1.ParameterName);
                }
            }

            string cmdText = string.Format(format, formatArgs.ToArray());

            CommandInfo cmdInfo = this.CreareCommandInfo(cmdText, parameters);

            return cmdInfo;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public CommandInfo CreareCommandInfo(string cmdText, System.Collections.Generic.List<IDataParameter> parameters)
        {
            return this.CreareCommandInfo(CommandType.Text, cmdText, parameters);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public CommandInfo CreareCommandInfo(CommandType cmdType, string cmdText, System.Collections.Generic.List<IDataParameter> parameters)
        {
            CommandInfo cmdInfo = new CommandInfo();
            cmdInfo.CommandText = cmdText;
            cmdInfo.CommandType = cmdType;
            cmdInfo.Parameters = parameters;
            if (cmdInfo.Connection == null)
                cmdInfo.Connection = this.Connection;
            if (cmdInfo.Transaction == null)
                cmdInfo.Transaction = this.Transaction;
            return cmdInfo;
        }

        #endregion

        #region ExecuteNonQuery/ExecuteNonQueryFormat

        /// <summary>
        /// ExecuteNonQueryFormat
        /// </summary>
        /// <param name="format"> 格式化命令文本 </param>
        /// <param name="args"> 格式化对象列表 </param>
        /// <returns></returns>
        public int ExecuteNonQueryFormat(string format, params object[] args)
        {
            return this.ExecuteNonQuery(this.CreareCommandInfoFormat(format, args));
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteNonQuery(this.CreareCommandInfo(cmdText, new System.Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="cmdText"> 命令文本</param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteNonQuery(this.CreareCommandInfo(cmdType, cmdText, new System.Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="cmdInfo"> 命令信息 </param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandInfo cmdInfo)
        {
            using (System.Data.IDbCommand cmd = this.Provider.CreateCommand())
            {
                cmdInfo.Update(cmd);
                return cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region ExecuteScalar/ExecuteScalarFormat

        /// <summary>
        /// ExecuteScalarFormat
        /// </summary>
        /// <param name="format"> 格式化命令文本 </param>
        /// <param name="args"> 格式化对象列表 </param>
        /// <returns></returns>
        public object ExecuteScalarFormat(string format, params object[] args)
        {
            return this.ExecuteScalar(this.CreareCommandInfoFormat(format, args));
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="cmdText"> 命令文本</param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteScalar(this.CreareCommandInfo(cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteScalar(this.CreareCommandInfo(cmdType, cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="cmdInfo"> 命令信息 </param>
        /// <returns></returns>
        public object ExecuteScalar(CommandInfo cmdInfo)
        {
            using (IDbCommand cmd = this.Provider.CreateCommand())
            {
                cmdInfo.Update(cmd);
                return cmd.ExecuteScalar();
            }
        }

        #endregion

        #region ExecuteReader/ExecuteReaderFormat

        /// <summary>
        /// ExecuteReaderFormat
        /// </summary>
        /// <param name="format"> 格式化命令文本 </param>
        /// <param name="args"> 格式化对象列表 </param>
        /// <returns></returns>
        public IDataReader ExecuteReaderFormat(string format, params object[] args)
        {
            return this.ExecuteReader(this.CreareCommandInfoFormat(format, args));
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteReader(this.CreareCommandInfo(cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return this.ExecuteReader(this.CreareCommandInfo(cmdType, cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="cmdInfo"> 命令信息 </param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandInfo cmdInfo)
        {
            using (IDbCommand cmd = this.Provider.CreateCommand())
            {
                cmdInfo.Update(cmd);
                return cmd.ExecuteReader();
            }
        }

        #endregion

        #region (填充数据集 DataTable)FillDataTable/FillDataTableFormat

        /// <summary>
        /// 填充数据集 DataTable
        /// </summary>
        /// <param name="format"> 格式代命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="args"> 格式化的对象列表 </param>
        /// <returns></returns>
        public int FillDataTableFormat(string format, DataTable source = null, params object[] args)
        {
            return this.FillDataTable(this.CreareCommandInfoFormat(format, args), source);
        }

        /// <summary>
        /// 填充数据集 DataTable
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int FillDataTable(string cmdText, DataTable source = null, params IDataParameter[] parameters)
        {
            return this.FillDataTable(this.CreareCommandInfo(cmdText, new Collections.Generic.List<IDataParameter>(parameters)), source);
        }

        /// <summary>
        /// 填充数据集 DataTable
        /// </summary>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int FillDataTable(CommandType cmdType, string cmdText, DataTable source = null, params IDataParameter[] parameters)
        {
            return this.FillDataTable(this.CreareCommandInfo(cmdType, cmdText, new Collections.Generic.List<IDataParameter>(parameters)), source);
        }

        /// <summary>
        /// 填充数据集 DataTable
        /// </summary>
        /// <param name="cmdInfo"> 命令信息 </param>
        /// <param name="source"> 数据源 </param>
        /// <returns></returns>
        public int FillDataTable(CommandInfo cmdInfo, DataTable source = null)
        {
            //  创建适配器
            using (System.Data.Common.DbDataAdapter dataAdapter = this.Provider.CreateDataAdapter())
            {
                if (source == null)
                    source = new DataTable();
                //  实例化一个内存表
                using (DataTable table = source)
                {
                    //  创建用于查询的命令
                    dataAdapter.SelectCommand = (System.Data.Common.DbCommand)this.Provider.CreateCommand();
                    //  更新查询命令参数
                    cmdInfo.Update(dataAdapter.SelectCommand);
                    //  填充数据
                    return dataAdapter.Fill(table);
                }
            }
        }

        #endregion

        #region (填充数据集 DataSet)FillDataSet/FillDataSetFormat

        /// <summary>
        /// 填充数据集 DataSet
        /// </summary>
        /// <param name="format"> 格式化命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="args"> 格式化对象列表 </param>
        /// <returns></returns>
        public int FillDataSetFormat(string format, DataSet source = null, params object[] args)
        {
            return this.FillDataSet(this.CreareCommandInfoFormat(format, args));
        }

        /// <summary>
        /// 填充数据集 DataSet
        /// </summary>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int FillDataSet(string cmdText, DataSet source = null, params IDataParameter[] parameters)
        {
            return this.FillDataSet(this.CreareCommandInfo(cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// 填充数据集 DataSet
        /// </summary>
        /// <param name="cmdType"> 命令类型 </param>
        /// <param name="cmdText"> 命令文本 </param>
        /// <param name="source"> 数据源 </param>
        /// <param name="parameters"> 命令参数列表 </param>
        /// <returns></returns>
        public int FillDataSet(CommandType cmdType, string cmdText, DataSet source = null, params IDataParameter[] parameters)
        {
            return this.FillDataSet(this.CreareCommandInfo(cmdType, cmdText, new Collections.Generic.List<IDataParameter>(parameters)));
        }

        /// <summary>
        /// 填充数据集 DataSet
        /// </summary>
        /// <param name="cmdInfo"> 命令信息 </param>
        /// <param name="source"> 数据源 </param>
        /// <returns></returns>
        public int FillDataSet(CommandInfo cmdInfo, DataSet source = null)
        {
            //  创建适配器
            using (System.Data.Common.DbDataAdapter dataAdapter = this.Provider.CreateDataAdapter())
            {
                //  创建数据缓存
                using (DataSet dataSet = new DataSet())
                {
                    //  创建用于查询的命令
                    dataAdapter.SelectCommand = (System.Data.Common.DbCommand)this.Provider.CreateCommand();
                    //  更新查询命令参数
                    cmdInfo.Update(dataAdapter.SelectCommand);
                    //  填充数据缓存
                    return dataAdapter.Fill(dataSet);
                }
            }
        }

        #endregion

        #region Open/Close

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public RValue<bool> Open()
        {
            RValue<bool> rValue = new RValue<bool>(true);
            try
            {
                if (this.Connection == null)
                {
                    this.Connection = this.Provider.CreateConnection();
                    this.Connection.ConnectionString = this.ConnectionString;
                }
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Close();
                    this.Connection.Open();
                }
                if (this.Connection.State == ConnectionState.Open)
                    return new RValue<bool>(true, "数据库连接成功!");
                else
                    return new RValue<bool>(false, false, "数据库连接失败!");
            }
            catch (Exception ex)
            {
                rValue = new RValue<bool>(false, false, ex.Message, ex);
            }
            return rValue;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public RValue<bool> Close()
        {
            RValue<bool> rValue = new RValue<bool>(true);
            try
            {
                if (this.Connection.State != ConnectionState.Closed)
                    this.Connection.Close();
                if (this.Connection.State == ConnectionState.Closed)
                    return new RValue<bool>(true, "数据库连接关闭成功!");
                else
                    return new RValue<bool>(false, false, "数据库连接关闭失败!");
            }
            catch (Exception ex)
            {
                rValue = new RValue<bool>(false, false, ex.Message, ex);
            }
            return rValue;
        }

        #endregion

        #region BeginTransaction/Rollback/Commit

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <returns></returns>
        public RValue<bool> BeginTransaction()
        {
            return this.BeginTransaction(null);
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <param name="level">事务锁级别</param>
        /// <returns></returns>
        public RValue<bool> BeginTransaction(IsolationLevel? level)
        {
            RValue<bool> rValue = new RValue<bool>();
            try
            {
                //  开始数据库事务
                this.Transaction = level.HasValue ? this.Connection.BeginTransaction(level.Value) : this.Connection.BeginTransaction();
                if (this.Transaction != null)
                    rValue = new RValue<bool>(true, "数据库事务开启成功!");
                else
                    rValue = new RValue<bool>(false, false, "数据库事务开启失败!");
            }
            catch (Exception ex)
            {
                rValue = new RValue<bool>(false, false, ex.Message, ex);
            }
            return rValue;
        }

        /// <summary>
        /// 数据库事务回滚
        /// </summary>
        /// <returns></returns>
        public RValue<bool> Rollback()
        {
            RValue<bool> rValue = new RValue<bool>();
            try
            {
                if (this.Transaction == null)
                    rValue = new RValue<bool>(false, false, "数据库事务处理未初始化!", null);
                else
                {
                    this.Transaction.Rollback();
                    this.Transaction.Dispose();
                    this.Transaction = null;
                    rValue = new RValue<bool>(true, "数据库事务回滚成功!");
                }
            }
            catch (Exception ex)
            {
                rValue = new RValue<bool>(false, false, ex.Message, ex);
            }
            return rValue;
        }

        /// <summary>
        /// 数据库事务提交
        /// </summary>
        /// <returns></returns>
        public RValue<bool> Commit()
        {
            RValue<bool> rValue = new RValue<bool>();
            try
            {
                if (this.Transaction == null)
                    rValue = new RValue<bool>(false, false, "数据库事务处理未初始化!", null);
                else
                {
                    this.Transaction.Commit();
                    this.Transaction.Dispose();
                    this.Transaction = null;
                    rValue = new RValue<bool>(true, "数据库事务提交成功!");
                }
            }
            catch (Exception ex)
            {
                rValue = new RValue<bool>(false, false, ex.Message, ex);
            }
            return rValue;
        }

        #endregion
    }
}
